﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class DataOperater
    {
        //定义连接字符串
        public static string strsqlconn = @"Data Source=PC-HUSON0830_TI\SQLEXPRESS;Initial Catalog=Badminton;User ID=sa; Password=123456";
        //创建连接
        public static SqlConnection conn;
        public static SqlConnection createconn()
        {
            conn = new SqlConnection(strsqlconn);
            conn.Open();
            return conn;
        }
        //注册操作员
        public static bool RegisterOperator(Model.Operator op)
        { 
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RegisterOp";
            cmd.Parameters.Add("@Operator_name",SqlDbType.NChar,25,"Operator_name");
            cmd.Parameters.Add("@Operator_passwd",SqlDbType.NChar,25,"Operator_passwd");
            cmd.Parameters.Add("@Operator_IC",SqlDbType.NChar,25,"Operator_IC");
            cmd.Parameters.Add("@Operator_Permissions",SqlDbType.Int,8,"Operator_Permissions");
            cmd.Parameters.Add("@Operator_tel",SqlDbType.NChar,25,"Operator_tel");

            cmd.Parameters["@Operator_name"].Value = op.Operator_name;
            cmd.Parameters["@Operator_passwd"].Value = op.Operator_passwd;
            cmd.Parameters["@Operator_IC"].Value = op.Operator_IC;
            cmd.Parameters["@Operator_Permissions"].Value = op.Operator_Permissions;
            cmd.Parameters["@Operator_tel"].Value = op.Operator_tel;

            cmd.Parameters.Add("@re_pwd", SqlDbType.Int, 8);
            cmd.Parameters["@re_pwd"].Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            if (Convert.ToInt32(cmd.Parameters["@re_pwd"].Value) == 0)
            {
                return true;
            }
            else
            {
                return false;

            }
            conn.Close(); 

        }
        //执行验证操作员登陆的认证
        public static bool ExecCheckOperaterLoginProc(Model.Operator op)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "CheckOperaterLogin";
            cmd.Parameters.Add("@Operator_name",SqlDbType.NChar,25,"Operator_name");
            cmd.Parameters.Add("@Operator_passwd",SqlDbType.NChar,25,"Operator_passwd");
            cmd.Parameters.Add("@Operator_IC",SqlDbType.NChar,25,"Operator_IC");

            cmd.Parameters["@Operator_name"].Value = op.Operator_name;
            cmd.Parameters["@Operator_passwd"].Value = op.Operator_passwd;
            cmd.Parameters["@Operator_IC"].Value = op.Operator_IC;

            cmd.Parameters.Add("@re_pwd", SqlDbType.Int, 8);
            cmd.Parameters["@re_pwd"].Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            if (Convert.ToInt32(cmd.Parameters["@re_pwd"].Value) == 0)
            {
                return true;
            }
            else
            {
                return false;

            }
            conn.Close(); 
        
        }
        //根据场地ID获取场地价格
        public static decimal GetCourtPrice(int Court_ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetCourtPrice";
            cmd.Parameters.Add("@Court_ID",SqlDbType.Int,8,"Court_ID");
            cmd.Parameters["@Court_ID"].Value = Court_ID;
            cmd.Parameters.Add("@price",SqlDbType.Int,8,"price");
            cmd.Parameters["@price"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            decimal price = Convert.ToDecimal(cmd.Parameters["@price"].Value);
            return price;
            conn.Close(); 
        
        }
        //根据场地ID，获取当前该场地的状态
        public static string Court_status;
        public static string GetCourtStatus( int Court_ID )
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT  Court_status FROM dbo.Court WHERE Court_ID="+Court_ID;
            SqlDataReader reader=cmd.ExecuteReader();
            if (reader.Read())
            {
                Court_status=reader["Court_status"].ToString();    
            }
            return Court_status; 
            conn.Close();
        }
        //订场
        public static bool BookingInsert(Model.Booking b)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "BookingInsert";

            cmd.Parameters.Add("@BUser_name", SqlDbType.NVarChar, 25, "BUser_name");
            cmd.Parameters.Add("@Court_ID", SqlDbType.Int, 8, "Court_ID");
            cmd.Parameters.Add("@price", SqlDbType.Money, 12, "price");
            cmd.Parameters.Add("@BUser_blance", SqlDbType.NVarChar, 25, "BUser_blance");
            cmd.Parameters.Add("@BUser_tel", SqlDbType.NVarChar, 25, "BUser_tel");
            cmd.Parameters.Add("@Court_light", SqlDbType.Char, 2, "Court_light");
            cmd.Parameters.Add("@Operator_name", SqlDbType.NVarChar, 25, "Operator_name");
            
            cmd.Parameters["@BUser_name"].Value = b.BUser_name;
            cmd.Parameters["@Court_ID"].Value = b.Court_ID;
            cmd.Parameters["@price"].Value =b.Price;
            cmd.Parameters["@BUser_blance"].Value = b.BUser_blance;
            cmd.Parameters["@BUser_tel"].Value = b.BUser_tel;

            cmd.Parameters["@Court_light"].Value = b.Court_light;
            cmd.Parameters["@Operator_name"].Value = b.Operator_name;

            cmd.Parameters.Add("@re_value", SqlDbType.Int, 8);
            cmd.Parameters["@re_value"].Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            if (Convert.ToInt32(cmd.Parameters["@re_value"].Value) == 1)
            { 
                return true;
            }
            else
            { 
                 return false;
            }
            conn.Close(); 

        
        }
        //拿到更新后的场地状态
        public static string GetChangeCourtStatus(int Court_ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = createconn();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT  Court_status FROM dbo.Court WHERE Court_ID=" + Court_ID;
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Court_status = reader["Court_status"].ToString();
            }
            return Court_status;
            conn.Close();
        }
    }
}
